package com.jd.sunny.jdwork;

import java.io.File;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Iterator;
import com.jd.sunny.util.DBConnectionUtil;
import com.jd.sunny.util.FileUtil;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;


/**
 *
 * @Description:
 * @author :Sun Lin
 * @date :2012-10-16
 * @since:1.0.0
 */
public class ErrorCode {

    public static Connection connection = DBConnectionUtil.getConnection();
    public static ExcelHandle excelHandle = new ExcelHandle();
    public static  int SEQ_SYS_ERROR_CODE_PRIMARYKEY = 1;
    public static  int SEQ_SYS_BANK_CODE_PRIMARYKEY = 1;

    /**
     * @param args
     * @throws java.sql.SQLException
     * @throws java.io.IOException
     */
    public static void main(String[] args) throws SQLException, IOException {
        String url = "C:/Users/Administrator/Desktop/支付平台错误编码-解析格式.xls";
        HSSFSheet hssfSheet = excelHandle.getHSSFSheet(url, 0);

        StringBuffer sql = new StringBuffer();
        Statement statement =  connection.createStatement();
        statement.execute("delete  from TBL_SYS_ERROR_CODE");
        statement.execute("delete  from TBL_BANK_ERROR_CODE");
        generateExposeErrorSql(sql,hssfSheet);
        generateInternalWithoutRelationErrorSql(sql,hssfSheet);
        generateInternalWithRelationErrorSql(sql,hssfSheet);
        generateBankErrorSql(sql,hssfSheet);
        connection.close();
        FileUtil.writeString(new File("C:/Users/Administrator/Desktop/支付平台错误编码-解析格式.sql"), sql.toString());

    }

    /**
     * 获取第一个工作表展示给用户的错误码
     * @throws java.sql.SQLException
     * @throws java.io.IOException
     */
    public static void  generateExposeErrorSql(StringBuffer sql, HSSFSheet hssfSheet) throws SQLException, IOException {

        Statement statement =  connection.createStatement();
        // 第0个工作表,展示给用户的错误码
        //获取所有行
        Iterator<Row> iteratorRow = hssfSheet.iterator();
        while(iteratorRow.hasNext()){
            Row row = iteratorRow.next();
            Cell cellCode = row.getCell(0);
            Cell cellName= row.getCell(1);
            //获取单元格数据
            String codeString = "";
            //空值就结束
            if(cellCode==null || cellName==null){
                continue;
            }

            if(cellCode.getCellType()==Cell.CELL_TYPE_STRING){
                codeString  =  cellCode.getStringCellValue();
            }else if (cellCode.getCellType()==Cell.CELL_TYPE_NUMERIC) {
                codeString = String.valueOf((long)cellCode.getNumericCellValue());
            }
            String nameString = cellName.getStringCellValue();
            //为空的一行跳过，有值的行才插入数据库
            if(codeString==null || codeString.length()<=0 || nameString==null || nameString.length()<=0){
                continue;
            }else {
                //拼凑sql，并执行,TYPE为1，DISPLAYABLE为1可展示，DISPLAY_ID为空，因为本身就是要展示给用户的错误,HAS_VALID为1有效
                String tempSql = "insert into TBL_SYS_ERROR_CODE" +
                        "(ID,TYPE,CODE,NAME,DESCRIPTION,DISPLAYABLE,HAS_VALID,CREATED,CREATED_DATE,CREATED_IP," +
                        "MODIFIED,MODIFIED_DATE,MODIFIED_IP)values(" + SEQ_SYS_ERROR_CODE_PRIMARYKEY + ",0, '" +
                        codeString + "','" +nameString+"','" + nameString + "',1,1," +
                        "'孙林',sysdate,'127.0.0.1','孙林',sysdate,'127.0.0.1')";
                System.out.println(tempSql);
                statement.execute(tempSql);
                sql = sql.append(tempSql+";\n");
                SEQ_SYS_ERROR_CODE_PRIMARYKEY ++;
            }
        }
        statement.close();
    }

    /**
     * 获取第二个工作表,系统内部没有关联的不可展示异常
     * @param sql
     * @throws java.io.IOException
     * @throws java.sql.SQLException
     */
    public static void  generateInternalWithoutRelationErrorSql(StringBuffer sql, HSSFSheet hssfSheet) throws IOException, SQLException{
        Statement statement =  connection.createStatement();
        //获取所有行
        Iterator<Row> iteratorRow = hssfSheet.iterator();
        while(iteratorRow.hasNext()){
            Row row = iteratorRow.next();
            Cell cellCode= row.getCell(2);
            Cell cellName = row.getCell(3);
            //获取单元格数据
            String codeString = "";
            if(cellCode==null || cellName==null){
                continue;
            }
            if(cellCode.getCellType()==Cell.CELL_TYPE_STRING){
                codeString  =  cellCode.getStringCellValue();
            }else if (cellCode.getCellType()==Cell.CELL_TYPE_NUMERIC) {
                codeString = String.valueOf((long)cellCode.getNumericCellValue());
            }
            String nameString = cellName.getStringCellValue();
            if(codeString==null || codeString.length()<=0 || nameString==null || nameString.length()<=0 || codeString.contains("JPS") ||
             codeString.contains("BANK") || codeString.contains("ROUTER")){
                continue;
            }else {
                //拼凑sql，并执行,TYPE为0，系统异常，DISPLAYABLE为0不可展示，DISPLAY_ID为空，因为没有关联的异常,HAS_VALID为1有效
                String tempSql = "insert into TBL_SYS_ERROR_CODE" +
                        "(ID,TYPE,CODE,NAME,DESCRIPTION,DISPLAYABLE,HAS_VALID,CREATED,CREATED_DATE,CREATED_IP," +
                        "MODIFIED,MODIFIED_DATE,MODIFIED_IP)values(" + SEQ_SYS_ERROR_CODE_PRIMARYKEY + ",0, '" + codeString + "','" +nameString+"','" + nameString + "',0,1," +
                        "'孙林',sysdate,'127.0.0.1','孙林',sysdate,'127.0.0.1')";
                System.out.println(tempSql);
                statement.execute(tempSql);
                sql = sql.append(tempSql+";\n");
                SEQ_SYS_ERROR_CODE_PRIMARYKEY++;
            }
        }
        statement.close();
    }

    /**
     * 获取第三个工作表,系统内部有关联不可展示异常
     * @param sql
     * @throws java.io.IOException
     * @throws java.sql.SQLException
     */
    public static void  generateInternalWithRelationErrorSql(StringBuffer sql, HSSFSheet hssfSheet) throws IOException, SQLException{
        Statement statement =  connection.createStatement();
        //获取所有行
        Iterator<Row> iteratorRow = hssfSheet.iterator();
        while(iteratorRow.hasNext()){
            Row row = iteratorRow.next();
           Cell cellExternalCode= row.getCell(0);
            Cell cellInternalCode = row.getCell(2);
            Cell cellName = row.getCell(3);
            if(cellExternalCode==null ||cellInternalCode==null|| cellName==null){
                continue;
            }
            //获取单元格数据
            String codeExternalString = "";
            if(cellExternalCode.getCellType()==Cell.CELL_TYPE_STRING){
                codeExternalString  =  cellExternalCode.getStringCellValue();
            }else if (cellExternalCode.getCellType()==Cell.CELL_TYPE_NUMERIC) {
                codeExternalString = String.valueOf((long)cellExternalCode.getNumericCellValue());
            }
            String codeInternalString = cellInternalCode.getStringCellValue();
            String nameString  = cellName.getStringCellValue();
            if(codeExternalString==null || codeExternalString.length()<=0 || codeInternalString==null ||
                    codeInternalString.length()<=0|| nameString==null || nameString.length()<=0){
                continue;
            }else {
                //查询id
                int id = 0;
                PreparedStatement preparedStatement =  connection.prepareStatement("select id,code from TBL_SYS_ERROR_CODE where code = ?");
                preparedStatement.setString(1, codeExternalString);
                ResultSet resultSet = preparedStatement.executeQuery();
                while(resultSet.next()){
                    id = resultSet.getInt("id");
                }
                String tempSql = "insert into TBL_SYS_ERROR_CODE(ID,TYPE,CODE,NAME,DESCRIPTION,DISPLAYABLE,DISPLAY_ID,HAS_VALID,CREATED,CREATED_DATE,CREATED_IP," +
                        "MODIFIED,MODIFIED_DATE,MODIFIED_IP)values(" + SEQ_SYS_ERROR_CODE_PRIMARYKEY + ",0, '" + codeInternalString + "','" +nameString+"','" + nameString + "',0,'" +id + "',1," +
                        "'孙林',sysdate,'127.0.0.1','孙林',sysdate,'127.0.0.1')";
                System.out.println(tempSql);
                statement.execute(tempSql);
                preparedStatement.close();
                sql = sql.append(tempSql+";\n");
                SEQ_SYS_ERROR_CODE_PRIMARYKEY++;
            }
        }
        statement.close();
    }

    /**
     * 获取第四个工作表,银行错误代码有关联
     * @param sql
     * @throws java.io.IOException
     * @throws java.sql.SQLException
     */
    public static void  generateBankErrorSql(StringBuffer sql, HSSFSheet hssfSheet) throws IOException, SQLException{
        Statement statement =  connection.createStatement();
        //获取所有行
        Iterator<Row> iteratorRow = hssfSheet.iterator();
        while(iteratorRow.hasNext()){
            Row row = iteratorRow.next();
            Cell cellExternalCode= row.getCell(2);
            Cell cellBankCode = row.getCell(4);
            Cell cellName = row.getCell(5);
            if(cellExternalCode==null ||cellBankCode==null|| cellName==null){
                continue;
            }
            //获取单元格数据
            String codeExternalString = "";
            if(cellExternalCode.getCellType()==Cell.CELL_TYPE_STRING){
                codeExternalString  =  cellExternalCode.getStringCellValue();
            }else if (cellExternalCode.getCellType()==Cell.CELL_TYPE_NUMERIC) {
                codeExternalString = String.valueOf((long)cellExternalCode.getNumericCellValue());
            }
            String bankCodeString = cellBankCode.getStringCellValue();
            String nameString  = cellName.getStringCellValue();
            if(codeExternalString==null || codeExternalString.length()<=0 || bankCodeString==null ||
                    bankCodeString.length()<=0|| nameString==null || nameString.length()<=0){
                continue;
            }else {
                //查询id
                int id = 0;
                PreparedStatement preparedStatement =  connection.prepareStatement("select id,code from TBL_SYS_ERROR_CODE where code = ?");
                preparedStatement.setString(1, codeExternalString);
                ResultSet resultSet = preparedStatement.executeQuery();
                while(resultSet.next()){
                    id = resultSet.getInt("id");
                }
                String tempSql = "insert into TBL_BANK_ERROR_CODE(ID,SYS_ERROR_CODE_ID,SETTLEMENT_BANK_ID,CODE,NAME,DESCRIPTION,HAS_VALID,CREATED,CREATED_DATE,CREATED_IP,MODIFIED,MODIFIED_DATE,MODIFIED_IP)" +
                        "values(" + SEQ_SYS_BANK_CODE_PRIMARYKEY+ ","+ id+",10000001,'" +bankCodeString+ "','"+nameString+"','"+nameString+"',1,'孙林',sysdate,'127.0.0.1','孙林',sysdate,'127.0.0.1')";
                System.out.println(tempSql);
                statement.execute(tempSql);
                preparedStatement.close();
                sql = sql.append(tempSql+";\n");
                SEQ_SYS_BANK_CODE_PRIMARYKEY++;
            }
        }
        statement.close();
    }

}
